package com.matt.repository.impl;

import com.matt.foundation.utils.DateUtil;
import com.matt.foundation.vo.IconTargetVo;
import com.matt.foundation.vo.StatisticsVo;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.StatisticsNativeRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * **********************
 * Created by Ron
 * from com.matt
 * on 2018/7/17 0017.
 * **********************
 */
@Service
public class StatisticsNativeRepositoryImpl extends BaseNativeSqlRepository implements StatisticsNativeRepository {

    @Override
    public StatisticsVo findNewRecords(String areaId,String beginDate, String endDate) throws ParseException {
        String begin=beginDate+" 00:00:00";
        String end = endDate+" 23:59:59";

        StatisticsVo statisticsVo=new StatisticsVo();
       //线索新增数量
        String sqlQueryle = String.format("select count(id) as leadsCount from leads where true and  created>='%s'",begin );
        sqlQueryle+=String.format(" and created<='%s'",end);
        sqlQueryle +=String.format(" and areaId='%s'",areaId);
        //客户新增数量
        String sqlQuerycl= String.format("select count(id) as clienteleCount from clientele where true and created>='%s'",begin);
        sqlQuerycl+=String.format(" and created<='%s'",end);
        sqlQuerycl +=String.format(" and areaId='%s'",areaId);
        //跟进新增数量
        String sqlQuerylh= String.format("select count(id) as followCount from leads_history where true and created>='%s'",begin);
        sqlQuerylh +=String.format(" and created<='%s'",end);
        sqlQuerylh +=String.format(" and areaId='%s'",areaId);
        //合同新增数量
        String sqlQuerycc= String.format("select count(id) as contractCount from contract where true and created>='%s'",begin);
        sqlQuerycc+=String.format(" and created<='%s'",end);
        sqlQuerycc +=String.format(" and areaId='%s'",areaId);
       //合同总金额
        String sqlQueryco= String.format("select sum(totalAmount) from contract where true and created>='%s'",begin);
        sqlQueryco+=String.format(" and created<='%s'",end);
        sqlQueryco +=String.format(" and areaId='%s'",areaId);
        //已预约客户 status==2
        String sqlQueryclienA= String.format("select count(id) from clientele where true and status=2 ");
        sqlQueryclienA +=String.format(" and areaId='%s'",areaId);
        //待面试客户数 status==4
        String sqlQueryclienB= String.format("select count(id) from clientele where true and  status=4 ");
        sqlQueryclienB +=String.format(" and areaId='%s'",areaId);
        //待补款客户
        String sqlQueryclienC=String.format("select count(DISTINCT(clienteleId)) from contract where payStatus=0 ");
        sqlQueryclienC +=String.format(" and areaId='%s'",areaId);
        //待续约客户
        String sqlQueryclienD=String.format("SELECT count(a.cId) FROM (select DISTINCT(cp.clienteleId) as cId from contract_product as cp inner join product as pr on cp.productId=pr.id inner join clientele as cli on cli.id=cp.clienteleId" +
                " inner join contract as con on con.id=cp.contractId  where pr.type=1 and TO_DAYS(con.endDate)-TO_DAYS(NOW())<30 and TO_DAYS(con.endDate)-TO_DAYS(NOW())>0 and con.status=1 and cli.areaId='%s'",areaId);
        sqlQueryclienD +=String.format(" union ");
        sqlQueryclienD += String.format(" select DISTINCT(cp.clienteleId) AS cId from contract_product as cp inner join product as pr on cp.productId=pr.id inner join clientele as cli on cli.id=cp.clienteleId " +
                " INNER JOIN contract as con ON cp.contractId=con.id where pr.type=2 and con.status=1 and cp.remainCount<8 and cp.remainCount>0 and cli.areaId='%s'",areaId);
        sqlQueryclienD +=String.format(")as a ");
        //待处理投诉


        //线索新增数量
        statisticsVo.setLeadsCount(sqlSingleList(sqlQueryle).size()> 0 ? Long.parseLong(sqlSingleList(sqlQueryle).get(0).toString()):new Long(0) );
        //客户新增数量
        statisticsVo.setClienteleCount(sqlSingleList(sqlQuerycl).size()>0 ? Long.parseLong(sqlSingleList(sqlQuerycl).get(0).toString()):new Long(0));
        //跟进新增数量
        statisticsVo.setFollowCount(sqlSingleList(sqlQuerylh).size()>0 ? Long.parseLong(sqlSingleList(sqlQuerylh).get(0).toString()):new Long(0));
        //合同新增数量
        statisticsVo.setContractCount(sqlSingleList(sqlQuerycc).size()>0 ? Long.parseLong( sqlSingleList(sqlQuerycc).get(0).toString()):new Long(0));
        //合同总金额
        if (sqlSingleList(sqlQueryco).size()>0 && sqlSingleList(sqlQueryco).get(0)!=null )
             statisticsVo.setContractSumMoney(sqlSingleList(sqlQueryco).size()>0 ? new BigDecimal(sqlSingleList(sqlQueryco).get(0).toString()):new BigDecimal(0));
        //已预约客户 status==2
        statisticsVo.setBookClienteleCount(sqlSingleList(sqlQueryclienA).size()>0 ? Long.parseLong( sqlSingleList(sqlQueryclienA).get(0).toString()):new Long(0));
        //待面试客户数 status==4
        statisticsVo.setInterviewClienteleCount(sqlSingleList(sqlQueryclienB).size()>0 ? Long.parseLong( sqlSingleList(sqlQueryclienB).get(0).toString()):new Long(0));
        //待补款客户
        statisticsVo.setFillMoneyClienteleCount(sqlSingleList(sqlQueryclienC).size()>0 ? Long.parseLong( sqlSingleList(sqlQueryclienC).get(0).toString()):new Long(0));
        //待续约客户
        statisticsVo.setRenewalClienteleCount(sqlSingleList(sqlQueryclienD).size()>0 ? Long.parseLong( sqlSingleList(sqlQueryclienD).get(0).toString()):new Long(0));
        //待处理投诉

        return statisticsVo;
    }

    @Override
    public List<IconTargetVo> getFiverStaffInfo(String areaId, String beginDate, String endDate) {
        String begin=beginDate+" 00:00:00";
        String end = endDate+" 23:59:59";

        //String sqlQuery=String.format("SELECT staffId as staffId,SUM(totalAmount) as staffAchievementCount from contract where areaId='%s' and  staffId IN(%s)",areaId,staffIds);
        String sqlQuery=String.format("SELECT staffId as staffId,SUM(totalAmount) as staffAchievementCount from contract where areaId='%s'",areaId);
        sqlQuery +=String.format(" and created >='%s'",begin);
        sqlQuery +=String.format(" and created <='%s'",end);
        sqlQuery +=String.format(" GROUP BY staffId ORDER BY SUM(totalAmount) DESC LIMIT 0,5");

        List<IconTargetVo> list=sqlObjectList(sqlQuery,IconTargetVo.class);
        return list;
    }

    @Override
    public BigDecimal actualPerformance(String staffId,String areaId, String date) {
        String sqlQuery=String.format("select sum(co.totalAmount) from contract as co where true and co.payStatus=1 and extract(YEAR_MONTH FROM co.created)='%s'",date);

        //个人
        if (StringUtils.isNotBlank(staffId))
            sqlQuery +=String.format(" and co.staffId='%s'",staffId);

        //园区
        if (StringUtils.isNotBlank(areaId))
            sqlQuery +=String.format(" and co.areaId='%s'",areaId);

        List list=sqlSingleList(sqlQuery);
        if (list.size()>0)
            return list.get(0)==null ? new BigDecimal(0):new BigDecimal(list.get(0).toString());
        else
            return new BigDecimal(0);
    }

    @Override
    public BigDecimal aimPerformance(String staffId, String areaId, String date) {
        String sqlQuery=String.format("select spd.amount as amount from stat_plan_data as spd where true and spd.type=1 and extract(YEAR_MONTH FROM spd.date)='%s' and spd.areaId='%s'",date,areaId);
        List list=sqlSingleList(sqlQuery);
        if (list.size()>0)
            return list.get(0)==null ? new BigDecimal(0):new BigDecimal(list.get(0).toString());
        else
            return new BigDecimal(0);
    }
}
